# -*- coding: utf-8 -*-
"""
@Time: 12/9/2018 4:12 PM
@Author: hejing
@Email: 2010jing@gmail.com
"""
from multiprocessing import Pool
import os, time, random
import pymysql
import time, requests
from concurrent.futures import ProcessPoolExecutor
import time
import random


def long_time_task(data):
	print('Run task %s (%s)...' % (os.getpid(), os.getpid()))
	start = time.time()
	data_handler(data)
	# with ProcessPoolExecutor() as excute:
	# 	excute.map(data_handler, data)
	end = time.time()
	print('Task %s runs %0.2f seconds.' % (os.getpid(), (end - start)))


# {"s_Controller_id":6, "LightPole_id":212, "Controller_id":24, "Read_time":"2017-04-06 16:03:00", "Concentrator_name":"\u6d4b\u8bd5\u96c6\u4e2d\u5668", "Concentrator_address":"", "Electric_quantity":1.55, "Electric_voltage":225.90001, "Electric_current":0.046000004, "Power":3.0, "Power_factor":0.0, "OneSwitch_status":0, "TwoSwitch_status":0, "Fault_status":1, "LightOn_duration":0, "brightness":100},

def data_handler(data):
	# conn = pymysql.connect(host='localhost', user='root', password='', database='gwsl', charset='utf8')
	conn = pymysql.connect(host='120.79.254.19', user='root', password='zsdx2016', database='gwsl', charset='utf8')
	cursor = conn.cursor()
	print(data[0])
	print(data[1])
	# s_Controller_id 6
	# LightPole_id 212 / 208 / 13 / 12 /11
	# Controller_id   (150) 216 / 215 / 214 / 213 / 212 / 211 / 210 / 209 / 208 / 207 / 206 / 205 / 204 / 202 / 200 / 199 / 197 / 195 / 194 / 193 / 192 / 190 / 189 / 187 / 185

	# Read_time 2017-04-06 16:03:00
	# Concentrator_name 测试集中器001
	# Concentrator_address 测试集中器地址001

	# Electric_quantity 1--43 ?
	# Electric_voltage  0~ 220~ 245?
	# Electric_current 0 ~ 0.189
	# Power 0 ~ 46
	# Power_factor 0.0
	# OneSwitch_status 0
	# TwoSwitch_status 0
	# Fault_status 1
	# LightOn_duration 0
	# brightness 0 --100

	for i in range(data[0], data[1]):
		sql = 'insert into search_controller(LightPole_id,Controller_id,Read_time, Concentrator_name, Concentrator_address, Electric_quantity,Electric_voltage,Electric_current,Power,Power_factor,OneSwitch_status,TwoSwitch_status,Fault_status,LightOn_duration,brightness) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);'

		res = cursor.execute(sql, [212, 24, '2017-04-06 16:03:00', '实验ControllerDemo', 'T8-501', ])
		conn.commit()
	cursor.close()
	conn.close()


def randomDateTime():
	a1 = (2018, 1, 1, 0, 0, 0, 0, 0, 0)  # 设置开始日期时间元组（1976-01-01 00：00：00）
	a2 = (2018, 12, 31, 23, 59, 59, 0, 0, 0)  # 设置结束日期时间元组（1990-12-31 23：59：59）

	start = time.mktime(a1)  # 生成开始时间戳
	end = time.mktime(a2)  # 生成结束时间戳

	t = random.randint(start, end)  # 在开始和结束时间戳中随机取出一个
	date_touple = time.localtime(t)  # 将时间戳生成时间元组
	datetime = time.strftime("%Y-%m-%d %H:%M:%S", date_touple)  # 将时间元组转成格式化字符串（1976-05-21）
	# print(datetime)
	return datetime


# create new concentrator
def createNewConcentrator():
	# conn = pymysql.connect(host='localhost', user='root', password='', database='gwsl', charset='utf8')
	conn = pymysql.connect(host='120.79.254.19', user='root', password='zsdx2016', database='gwsl', charset='utf8')
	cursor = conn.cursor()

	sql = "INSERT INTO `t_concentrator` (`cid`, `cname`, `caddress`, `rid`, `communication_type`, `Wiring_method`, `Type`, `Server_IP`, `Monitor_port`, `Server_port`, `Net_id`, `Router_address`, `Router_channel`, `Sim_no`, `Electricity_fee`, `Light_on_brightness`, `Light_off_brightness`, `longitude`, `latitude`, `Captital_id`, `address`, `phones`, `Message_notification`, `start`, `Issue_sunup_sunset_timetable`, `Light_operated_detection`, `Dianbi`, `Phase_a_current`, `Phase_b_current`, `Phase_c_current`, `All_night_light_a_current_reference`, `All_night_light_b_current_reference`, `All_night_light_c_current_reference`, `midnight_light_a_current_reference`, `midnight_light_b_current_reference`, `midnight_light_c_current_reference`, `view_light_a_current_reference`, `view_light_b_current_reference`, `view_light_c_current_reference`, `floodlight_a_current_reference`, `floodlight_b_current_reference`, `floodlight_c_current_reference`, `Error_repeat_time`, `Ver`, `Manufacturer`, `remarks`, `A2`, `A1`, `status`, `image`, `beforeEnergySaved`) VALUES (NULL, '测试测试', '', '2', 'Carrier', 'All night light', 'Type one', '', '0', '0', '', '', '0', '', '0.0000', '0', '0', '113.619204', '22.652918', '', '', '', '0', '0', '0', '0', '', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0', '', '', 'frog', '10022', '756', '4', '/Image_concentrator/concentrator_Demo.png', '')"

	res = cursor.execute(sql)
	conn.commit()
	print(cursor.lastrowid)

	cursor.close()
	conn.close()

	concentratorId = cursor.lastrowid

	createNewLightPoleAndController(concentratorId)

def createNewLightPoleAndController(concentratorId):
	# conn = pymysql.connect(host='localhost', user='root', password='', database='gwsl', charset='utf8')
	conn = pymysql.connect(host='120.79.254.19', user='root', password='zsdx2016', database='gwsl', charset='utf8')
	cursor = conn.cursor()
	base_light_pole_id = 53;
	# base cid from t_controller
	_cid = get_t_controller_cid() + 1
	# base lid from t_lamp
	_lid = get_t_lamp_lid() + 1
	for i in range(0, 10000):
		cid = _cid + i
		# 灯杆
		# l_no  /  cid
		light_pole_sql = "INSERT INTO `t_light_pole` (`lid`, `l_no`, `cid`, `power_type`, `remarks`) VALUES (NULL, %s, %s, '全夜灯', 'frog')"

		res = cursor.execute(light_pole_sql,['T5-'+str(cid),concentratorId])
		conn.commit()
		print("pole_id:"+ str(cursor.lastrowid))
		light_pole_id = cursor.lastrowid
		if i == 0:
			base_light_pole_id = light_pole_id


		# 控制器
		# cid / pole_id / concentratorId / measuring_point_no
		controller_sql = "INSERT INTO `t_controller` (`cid`, `pid`, `power_type`, `Group_number`, `Net_id`, `Controller_way`, `Branch_number`, `Controller_channel`, `Controller_no`, `alias`, `Controller_type`, `port`, `Is_important`, `Loop1`, `Concentrator_id`, `model`, `manufacturer`, `remarks`, `measuring_point_no`, `caddress`, `status`, `monitoring`, `image`, `recent_online_time`) VALUES (%s, %s, '半夜灯', '一组', '0', '单路', '2', '64', '0', NULL, '类型一', '31', '0', '2路', %s, '3型', '格物', 'frog', %s, '201711255523', '16', '0', '/Image_controller/controller_Demo.png', '1543386247159')"
		measuring_point_no = 30 + i
		print("cid : " + str(cid))
		res = cursor.execute(controller_sql, [cid,light_pole_id, concentratorId, measuring_point_no])
		conn.commit()

		# 灯泡
		# lid  / concentrator_id  /  Measure_point / longitude / latitude

		lamp_sql = "INSERT INTO `t_lamp` (`lid`, `concentrator_id`, `Measure_point`, `Power_type`, `Controller_type`, `alias`, `Lamp_serial`, `Initializing_value`, `Remind_frequency`, `Line_Initializing_value`, `Line_Remind_frequency`, `Rated_capacity_after_modification`, `Second_rated_capacity`, `Original_capacity`, `Light_adjustment_start_time`, `Light_adjustment_end_time`, `Light_adjust_ratio`, `rated_illumination`, `longitude`, `latitude`, `manufacturer`, `model`, `Captital_id`, `Phase_sequence`, `Install_date`, `remarks`, `status`, `image`, `power_limit_hi`, `power_limit_low`) VALUES (%s, %s, %s, '全夜灯', '并联型', '灯具名称', '50', '0', '0', '0', '0', '0', '0', '0', '00:10', '13:22', '0', '0', %s, %s, '', '', '', '', '2018-12-21', 'frog', '0', '/Image_Lamp/Lamp_Demo.png', '20', '20')"

		lid = _lid + i
		longitude = 120 + lid/1000
		latitude = 25 + lid/1000
		print("lid : " + str(lid))

		res = cursor.execute(lamp_sql, [lid, concentratorId, measuring_point_no,longitude,latitude])
		conn.commit()

		print("created number : " + str(i))

	cursor.close()
	conn.close()

	# insert fake data into search_controller table
	# insertFakeRecords(base_light_pole_id)


# table : search_controller
def insertFakeRecords(base_light_pole_id):
	conn = pymysql.connect(host='localhost', user='root', password='', database='gwsl', charset='utf8')
	# conn = pymysql.connect(host='120.79.254.19', user='root', password='zsdx2016', database='gwsl', charset='utf8')
	cursor = conn.cursor()

	sql = "INSERT INTO `search_controller` (`s_Controller_id`, `LightPole_id`, `Controller_id`, `Read_time`, `Concentrator_name`, `Concentrator_address`, `Electric_quantity`, `Electric_voltage`, `Electric_current`, `Power`, `Power_factor`, `OneSwitch_status`, `TwoSwitch_status`, `Fault_status`, `LightOn_duration`, `brightness`) VALUES (NULL, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"

	# 120.79.254.19  concentrator id ===> 61  base_cid = 1000  base_pid = 53 max_cid = 100999
	base_cid = 1000
	# base_light_pole_id = 53 # t_light_pole ==》 lid new start one
	max_cid = 10000 + base_cid - 1
	for i in range(1, 100000000):
		# get LightPole_id -- Controller_id
		Controller_id = random.randint(base_cid, max_cid)

		LightPole_id = Controller_id - base_cid + base_light_pole_id

		# Read_time
		Read_time = randomDateTime()

		# Electric_quantity 1--43
		Electric_quantity = random.randint(1, 43)

		# Electric_voltage 220~ 245
		Electric_voltage = random.randint(220, 245)

		# Electric_current 0 ~ 0.189
		Electric_current = random.randint(0, 189)/1000

		# Power 0 ~ 46
		Power = random.randint(0, 46)

		res = cursor.execute(sql, [LightPole_id, Controller_id, Read_time, '测试服务器集中器', 'T8-501', Electric_quantity, Electric_voltage, Electric_current, Power, '1', '0', '0', '1', '0', '100'])

		conn.commit()

	cursor.close()
	conn.close()



# auto_increment : t_concentrator  /   t_light_pole
# non-auto_increment : t_controller  /  t_lamp

def get_t_controller_cid():
	# conn = pymysql.connect(host='localhost', user='root', password='', database='gwsl', charset='utf8')
	conn = pymysql.connect(host='120.79.254.19', user='root', password='zsdx2016', database='gwsl', charset='utf8')
	cursor = conn.cursor()

	sql = "SELECT cid FROM gwsl.t_controller order by cid desc limit 1;"

	res = cursor.execute(sql)
	result = cursor.fetchone()
	conn.commit()
	print('get_t_controller_cid:' + str(result[0]))
	cursor.close()
	conn.close()
	return result[0]

def get_t_lamp_lid():
	# conn = pymysql.connect(host='localhost', user='root', password='', database='gwsl', charset='utf8')
	conn = pymysql.connect(host='120.79.254.19', user='root', password='zsdx2016', database='gwsl', charset='utf8')
	cursor = conn.cursor()

	sql = "SELECT lid FROM gwsl.t_lamp order by lid desc limit 1;"

	res = cursor.execute(sql)
	result = cursor.fetchone()
	conn.commit()
	print('get_t_lamp_lid:' + str(result[0]))
	cursor.close()
	conn.close()
	return result[0]


if __name__ == '__main__':
	start = time.time()
	# datas = [(0, 2500000), (2500000, 5000000), (5000000, 7500000), (7500000, 10000000)]
	# datas = [(0, 250000), (250000, 500000), (500000, 750000), (750000, 1000000)]
	# datas = [(0, 2500), (2500, 5000), (5000, 7500), (7500, 10000)]
	# randomDateTime()
	createNewConcentrator()
	# insertFakeRecords(53)
	# aaa()

# print('Parent process %s.' % os.getpid())
# p = Pool(4)
# p.map(long_time_task, [i for i in datas])
# print('All subprocesses done.')
	end = time.time()
	print('Task %s runs %0.2f seconds.' % (os.getpid(), (end - start)))
